package paper.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import keter.domain.Account;
import keter.domain.Role;
import keter.domain.Unit;
import keter.exception.KeterValidationException;
import keter.util.PathUtil;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import paper.domain.User;

import com.google.common.collect.Lists;

public class XLSUtil {
    /**
     * Logger for this class
     */
    private static final Logger logger = LoggerFactory.getLogger(XLSUtil.class);

    public static void createXLSFromUser(String xlsFile) {

    }

    public static void createXLS(String xlsFile) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(); // 产生工作簿对象
            HSSFSheet sheet = workbook.createSheet(); // 产生工作表对象
            // 设置第一个工作表的名称为firstSheet
            // 为了工作表能支持中文，设置字符编码为UTF_16
            workbook.setSheetName(0, "firstSheet");
            // 产生一行
            HSSFRow row = sheet.createRow(0);
            // 产生第一个单元格
            HSSFCell cell = row.createCell(0);
            // 设置单元格内容为字符串型
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            // 往第一个单元格中写入信息
            cell.setCellValue("测试成功");
            FileOutputStream fOut = new FileOutputStream(xlsFile);
            workbook.write(fOut);
            fOut.flush();
            fOut.close();
            System.out.println("文件生成...");
            // 以下语句读取生成的Excel文件内容
            FileInputStream fIn = new FileInputStream(xlsFile);
            HSSFWorkbook readWorkBook = new HSSFWorkbook(fIn);
            HSSFSheet readSheet = readWorkBook.getSheet("firstSheet");
            HSSFRow readRow = readSheet.getRow(0);
            HSSFCell readCell = readRow.getCell(0);
            System.out.println("第一个单元是：" + readCell.getStringCellValue());
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static List<User> getAccountUserFromXLS(String fileUri) throws KeterValidationException, FileNotFoundException {
    	FileInputStream xlsFile = new FileInputStream(fileUri);
    	// 构造 XSSFWorkbook 对象，strPath 传入文件路径
        List<User> userList = Lists.newArrayList();
        XSSFWorkbook xwb = null;
        try {
            xwb = new XSSFWorkbook(xlsFile);
        } catch (IOException e) {
        	logger.error("xls文件解析异常:",e);
        }
        // 定义 row、cell
        XSSFRow row;
        Set<String> nameSet = new HashSet<String>();// 账户集合：用于重复校验
        // 读取sheet1内容：教师
        XSSFSheet sheet = xwb.getSheetAt(0);
        // 循环输出表格中的内容
        for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (i == 0) {// 标题行处理
                continue;
            }
            // 数据处理开始
            String accountName = getString(row.getCell(0)); // 帐户
            notSameNull(i, accountName);
            Account a = buildAccount(nameSet, i, accountName,"TEACHER");
            User u = buildUser(row, a);
            userList.add(u);
        }
        
        //读取sheet2内容：学生
        sheet = xwb.getSheetAt(1);
        nameSet = new HashSet<String>();// 账户集合：用于重复校验
        // 循环输出表格中的内容
        for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
        	row = sheet.getRow(i);
        	if (i == 0) {// 标题行处理
        		continue;
        	}
        	// 数据处理开始
        	String accountName = getString(row.getCell(0)); // 帐户
        	notSameNull(i, accountName);
        	Account a = buildAccount(nameSet, i, accountName,"STUDENT");
        	User u = buildUser(row, a);
        	userList.add(u);
        }
        return userList;
    }

	private static Account buildAccount(Set<String> nameSet, int i, String accountName, String roleName)
			throws KeterValidationException {
		Account a = new Account();
		a.setName(accountName);
		validateUnique(i, nameSet, accountName);
		a.setPassword("111111");
		a.setRepeatPassword(a.getPassword());
        Role role = new Role();
        role.setName(roleName);
        a.getRoleList().add(role);
		return a;
	}
	
	private static User buildUser(XSSFRow row, Account a) {
		User u = new User();
		u.setAccount(a);
		u.setName(getString(row.getCell(1))); // 名称
		u.setPhone(getNumber(row.getCell(2))); // 电话
		u.setEmail(getString(row.getCell(3))); // 邮件
        Unit unit = new Unit();
        unit.setCode(getString(row.getCell(4)));// 组织机构代码
        u.getAccount().setUnit(unit);
        u.setQQ(getString(row.getCell(5))); // QQ
		u.setSno(getString(row.getCell(6)));//学号
		u.setGraduate(getString(row.getCell(7)));//毕业年份
		return u;
	}

    /**
     * <p>
     * Method ：validateNotNull
     * <p>
     * Description : 校验账户、学号不同时为空
     * 
     * @param i
     * @param accountName
     * @param password
     * @throws KeterValidationException
     * @author gulixing@msn.com
     * @version 1.0.0
     */
    private static void notSameNull(int i, String accountName)
            throws KeterValidationException {
        if (StringUtils.isEmpty(accountName)) {
            throw new KeterValidationException("第" + (i + 1) + "行数据有误：账号不能为空！");
        }
    }

    /**
     * <p>
     * Method ：validateUnique
     * <p>
     * Description : 校验内容重复
     * 
     * @param i
     * @param set
     * @param content
     * @throws KeterValidationException
     * @author gulixing@msn.com
     * @version 1.0.0
     */
    private static void validateUnique(int i, Set<String> set, String content)
            throws KeterValidationException {
        if (set.contains(content)) {
            throw new KeterValidationException("第" + (i + 1) + "行数据有误：出现重复内容[" + content + "]！");
        }
        set.add(content);
    }

    /**
     * <p>
     * Method ：nullCell
     * <p>
     * Description : 
     * 
     * @param in
     * @return
     * @author 顾力行-gulixing@msn.com
     */
    private static String getString(XSSFCell in) {
    	if(in==null){
    		return "";
    	}
    	//防止傻逼POI把"123"变成"123.0"
    	if(in.getCellType() == Cell.CELL_TYPE_NUMERIC){
    		return Double.valueOf(in.toString()).longValue()+"";
    	}
        return in.toString(); 		
    }

    /**
     * <p>
     * Method ：converXLSNumberToString
     * <p>
     * Description : 将Cell中的数字转换成字符串，避免出现科学计数表达形式
     * 
     * @param in
     * @return
     * @author 顾力行-gulixing@msn.com
     */
    private static String getNumber(XSSFCell in) {
        if (in == null) {
            return "";
        }
        DecimalFormat df = new DecimalFormat("#");// 转换成整型
        return df.format(in.getNumericCellValue());
    }

    public static void main(String args[]) throws FileNotFoundException, IOException,
            KeterValidationException {
        Set s = new HashSet();
        s.add("1");
        s.add("2");
        System.out.println(s.contains("1"));

        // createXLS(xlsFile);
        System.out.println(PathUtil.getProjectRootWithoutFile());
        String xls = PathUtil.getProjectRootWithoutFile() + "/students.xlsx"; // 产生的Excel文件的名称
        List<User> auw = getAccountUserFromXLS(xls);
        for (Iterator<User> iterator = auw.iterator(); iterator.hasNext();) {
            User a = (User) iterator.next();
            logger.info(a.getName());
        }
    }

}